/*
Navicat MySQL Data Transfer

Source Server         : anh
Source Server Version : 50153
Source Host           : localhost:3306
Source Database       : quanlysach

Target Server Type    : MYSQL
Target Server Version : 50153
File Encoding         : 65001

Date: 2011-08-04 10:59:38
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for `chitiethoadon`
-- ----------------------------
DROP TABLE IF EXISTS `chitiethoadon`;
CREATE TABLE `chitiethoadon` (
`MaChiTietHoaDon`  int(11) NOT NULL AUTO_INCREMENT ,
`MaHoaDon`  int(11) NULL DEFAULT NULL ,
`MaSach`  varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
`SoLuongBan`  int(11) NULL DEFAULT NULL ,
`GiaBan`  float NULL DEFAULT NULL ,
PRIMARY KEY (`MaChiTietHoaDon`),
FOREIGN KEY (`MaSach`) REFERENCES `sach` (`MaSach`) ON DELETE RESTRICT ON UPDATE RESTRICT,
FOREIGN KEY (`MaHoaDon`) REFERENCES `hoadon` (`MaHoaDon`) ON DELETE RESTRICT ON UPDATE RESTRICT
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_unicode_ci
AUTO_INCREMENT=17

;

-- ----------------------------
-- Records of chitiethoadon
-- ----------------------------
BEGIN;
INSERT INTO `chitiethoadon` VALUES ('1', '3', 'MS001', '8', '0'), ('2', '4', 'MS001', '5', '50000'), ('3', '5', 'MS001', '5', '50000'), ('4', '6', 'MS001', '2', '50000'), ('5', '10', 'MS005', '4', '66'), ('6', '11', 'MS003', '3', '66'), ('7', '12', 'MS003', '3', '66'), ('8', '13', 'MS003', '3', '66'), ('9', '14', 'MS003', '3', '66'), ('10', '15', 'MS003', '3', '66'), ('11', '16', 'MS006', '2', '66'), ('12', '17', 'MS006', '2', '66'), ('13', '18', 'MS006', '2', '66'), ('14', '19', 'MS006', '2', '66'), ('15', '20', 'MS006', '2', '66'), ('16', '21', 'MS006', '3', '66');
COMMIT;

-- ----------------------------
-- Table structure for `chitietphieunhap`
-- ----------------------------
DROP TABLE IF EXISTS `chitietphieunhap`;
CREATE TABLE `chitietphieunhap` (
`MaChiTietPhieuNhap`  int(11) NOT NULL AUTO_INCREMENT ,
`MaPhieuNhap`  int(11) NULL DEFAULT NULL ,
`MaSach`  varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
`SoLuongNhap`  int(11) NULL DEFAULT NULL ,
`DonGiaNhap`  float(11,0) NULL DEFAULT NULL ,
PRIMARY KEY (`MaChiTietPhieuNhap`),
FOREIGN KEY (`MaSach`) REFERENCES `sach` (`MaSach`) ON DELETE RESTRICT ON UPDATE RESTRICT,
FOREIGN KEY (`MaPhieuNhap`) REFERENCES `phieunhap` (`MaPhieuNhap`) ON DELETE RESTRICT ON UPDATE RESTRICT
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_unicode_ci
AUTO_INCREMENT=12

;

-- ----------------------------
-- Records of chitietphieunhap
-- ----------------------------
BEGIN;
INSERT INTO `chitietphieunhap` VALUES ('5', '37', 'MS001', '5', '9'), ('6', '37', 'MS001', '5', '9'), ('7', '38', 'MS004', '86', '678978'), ('8', '39', 'MS006', '5', '899'), ('9', '40', 'MS007', '5', '66666'), ('10', '41', 'MS008', '3', '456678'), ('11', '42', 'MS009', '3', '455555');
COMMIT;

-- ----------------------------
-- Table structure for `hoadon`
-- ----------------------------
DROP TABLE IF EXISTS `hoadon`;
CREATE TABLE `hoadon` (
`MaHoaDon`  int(11) NOT NULL AUTO_INCREMENT ,
`NgayLapHoaDon`  date NULL DEFAULT NULL ,
`TongTien`  float NULL DEFAULT NULL ,
PRIMARY KEY (`MaHoaDon`)
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_unicode_ci
AUTO_INCREMENT=22

;

-- ----------------------------
-- Records of hoadon
-- ----------------------------
BEGIN;
INSERT INTO `hoadon` VALUES ('1', '2011-08-09', '12'), ('2', '0000-00-00', '10000'), ('3', null, '0'), ('4', null, '0'), ('5', null, '250000'), ('6', null, '100000'), ('7', '2011-09-18', '9999'), ('8', '2011-09-27', '999'), ('9', '2011-02-08', '77777'), ('10', '2011-04-08', '264'), ('11', '2011-04-08', '198'), ('12', '2011-04-08', '198'), ('13', '2011-04-08', '198'), ('14', '2011-04-08', '198'), ('15', '2011-04-08', '198'), ('16', '2011-04-08', '132'), ('17', '2011-04-08', '132'), ('18', '2011-04-08', '132'), ('19', '2011-04-08', '132'), ('20', '2011-04-08', '132'), ('21', '2011-04-08', '198');
COMMIT;

-- ----------------------------
-- Table structure for `nhaxuatban`
-- ----------------------------
DROP TABLE IF EXISTS `nhaxuatban`;
CREATE TABLE `nhaxuatban` (
`MaNhaXuatBan`  varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
`TenNhaXuatBan`  varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
PRIMARY KEY (`MaNhaXuatBan`)
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_unicode_ci

;

-- ----------------------------
-- Records of nhaxuatban
-- ----------------------------
BEGIN;
INSERT INTO `nhaxuatban` VALUES ('NXB001', 'Kim Dong'), ('NXB002', 'Khoa Hoc Tu Nhien');
COMMIT;

-- ----------------------------
-- Table structure for `phieunhap`
-- ----------------------------
DROP TABLE IF EXISTS `phieunhap`;
CREATE TABLE `phieunhap` (
`MaPhieuNhap`  int(11) NOT NULL AUTO_INCREMENT ,
`NgayNhap`  date NULL DEFAULT NULL ,
PRIMARY KEY (`MaPhieuNhap`)
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_unicode_ci
AUTO_INCREMENT=43

;

-- ----------------------------
-- Records of phieunhap
-- ----------------------------
BEGIN;
INSERT INTO `phieunhap` VALUES ('18', '2011-02-08'), ('29', '2011-02-02'), ('35', '2011-04-08'), ('36', '2011-04-08'), ('37', '2011-04-08'), ('38', '2011-04-08'), ('39', '2011-04-08'), ('40', '2011-04-08'), ('41', '2011-04-08'), ('42', '2011-04-08');
COMMIT;

-- ----------------------------
-- Table structure for `sach`
-- ----------------------------
DROP TABLE IF EXISTS `sach`;
CREATE TABLE `sach` (
`MaSach`  varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
`TenSach`  varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
`GiaBan`  float(11,0) NULL DEFAULT NULL ,
`SoLuongTon`  int(11) NULL DEFAULT NULL ,
`MaNhaXuatBan`  varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
`MaTheLoai`  int(11) NULL DEFAULT NULL ,
PRIMARY KEY (`MaSach`),
FOREIGN KEY (`MaNhaXuatBan`) REFERENCES `nhaxuatban` (`MaNhaXuatBan`) ON DELETE RESTRICT ON UPDATE RESTRICT,
FOREIGN KEY (`MaTheLoai`) REFERENCES `theloai` (`MaTheLoai`) ON DELETE RESTRICT ON UPDATE RESTRICT
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_unicode_ci

;

-- ----------------------------
-- Records of sach
-- ----------------------------
BEGIN;
INSERT INTO `sach` VALUES ('MS001', 'LT Java', '50000', '30', 'NXB001', '1'), ('MS002', 'LT Javađ', '50000', '20', 'NXB001', '1'), ('MS003', 'Ldddđ', '66', '1', 'NXB001', '1'), ('MS004', ' sach toan', '900000', '86', 'NXB001', '2'), ('MS005', 'anh em minh', '66', '1', 'NXB001', '1'), ('MS006', 'anh em minh', '66', '12', 'NXB001', '1'), ('MS007', 'Toan cao cap', '55555556', '5', 'NXB001', '2'), ('MS008', 'Van Hoc', '8888888', '3', 'NXB001', '3'), ('MS009', 'Van Hoc 1', '777777792', '3', 'NXB001', '3');
COMMIT;

-- ----------------------------
-- Table structure for `theloai`
-- ----------------------------
DROP TABLE IF EXISTS `theloai`;
CREATE TABLE `theloai` (
`MaTheLoai`  int(11) NOT NULL AUTO_INCREMENT ,
`TenTheLoai`  varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
PRIMARY KEY (`MaTheLoai`)
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_unicode_ci
AUTO_INCREMENT=4

;

-- ----------------------------
-- Records of theloai
-- ----------------------------
BEGIN;
INSERT INTO `theloai` VALUES ('1', 'Tin Hoc'), ('2', 'Toan Hoc'), ('3', 'Van Hoc');
COMMIT;

-- ----------------------------
-- Procedure structure for `sp_LayDanhSach_TheLoai`
-- ----------------------------
DROP PROCEDURE IF EXISTS `sp_LayDanhSach_TheLoai`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_LayDanhSach_TheLoai`()
BEGIN
	SELECT * FROM theloai ;
END
;;
DELIMITER ;

-- ----------------------------
-- Procedure structure for `sp_LayDanhSachNXB`
-- ----------------------------
DROP PROCEDURE IF EXISTS `sp_LayDanhSachNXB`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_LayDanhSachNXB`()
BEGIN
	#Routine body goes here...
SELECT * from nhaxuatban;

END
;;
DELIMITER ;

-- ----------------------------
-- Procedure structure for `sp_LayDSSach_TheoTheLoai`
-- ----------------------------
DROP PROCEDURE IF EXISTS `sp_LayDSSach_TheoTheLoai`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_LayDSSach_TheoTheLoai`(in _maTheLoai int)
BEGIN
	SELECT * FROM sach where MaTheLoai=_maTheLoai;
END
;;
DELIMITER ;

-- ----------------------------
-- Procedure structure for `sp_LayMax_MaHoaDon`
-- ----------------------------
DROP PROCEDURE IF EXISTS `sp_LayMax_MaHoaDon`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_LayMax_MaHoaDon`()
BEGIN
	select MAX(MaHoaDon) from hoadon;
END
;;
DELIMITER ;

-- ----------------------------
-- Procedure structure for `sp_LayMaxPhieuNhap`
-- ----------------------------
DROP PROCEDURE IF EXISTS `sp_LayMaxPhieuNhap`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_LayMaxPhieuNhap`()
BEGIN
	select MAX(MaPhieuNhap) from phieunhap;
END
;;
DELIMITER ;

-- ----------------------------
-- Procedure structure for `sp_LaySachTheo_MaTheLoai_MaNhaXuatBan`
-- ----------------------------
DROP PROCEDURE IF EXISTS `sp_LaySachTheo_MaTheLoai_MaNhaXuatBan`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_LaySachTheo_MaTheLoai_MaNhaXuatBan`(IN `_matheloai` int,IN `_manhaxuatban` varchar(50))
BEGIN
	#Routine body goes here...
SELECT * from sach where MaTheLoai=_matheloai and MaNhaXuatBan=_manhaxuatban;
END
;;
DELIMITER ;

-- ----------------------------
-- Procedure structure for `sp_LaySachTheoGiaBan_Tu_Den`
-- ----------------------------
DROP PROCEDURE IF EXISTS `sp_LaySachTheoGiaBan_Tu_Den`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_LaySachTheoGiaBan_Tu_Den`(IN `_giatu` float,IN `_giaden` float)
BEGIN
	#Routine body goes here...
SELECT * FROM sach where GiaBan >=_giatu AND GiaBan<=_giaden;
END
;;
DELIMITER ;

-- ----------------------------
-- Procedure structure for `sp_LaySachTheoMaSach`
-- ----------------------------
DROP PROCEDURE IF EXISTS `sp_LaySachTheoMaSach`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_LaySachTheoMaSach`(in _maSach VARCHAR(50))
BEGIN
	SELECT * FROM sach where MaSach=_maSach;
END
;;
DELIMITER ;

-- ----------------------------
-- Procedure structure for `sp_LaySachTheoTenSach`
-- ----------------------------
DROP PROCEDURE IF EXISTS `sp_LaySachTheoTenSach`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_LaySachTheoTenSach`(IN `_tensach` varchar(50))
BEGIN
	#Routine body goes here...
SELECT * from sach WHERE TenSach LIKE CONCAT('%', _tensach ,'%');

END
;;
DELIMITER ;

-- ----------------------------
-- Procedure structure for `sp_Them_CTPhieuNhap`
-- ----------------------------
DROP PROCEDURE IF EXISTS `sp_Them_CTPhieuNhap`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_Them_CTPhieuNhap`(IN `_maphieunhap` int,IN `_masach` varchar(50),IN `_soluongnhap` int,IN `_dongianhap` float,OUT `_machitietphieunhap` int)
BEGIN
	#Routine body goes here...
  INSERT INTO chitietphieunhap(MaPhieuNhap,MaSach,SoLuongNhap,DonGiaNhap)
   VALUES(`_maphieunhap` ,`_masach` ,`_soluongnhap` ,`_dongianhap`);
   SET _machitietphieunhap=LAST_INSERT_ID();
   
END
;;
DELIMITER ;

-- ----------------------------
-- Procedure structure for `sp_ThemCTPhieuNhap`
-- ----------------------------
DROP PROCEDURE IF EXISTS `sp_ThemCTPhieuNhap`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_ThemCTPhieuNhap`(IN `_maphieunhap` int,IN `_masach` varchar(50),IN `_soluongnhap` int,IN `_dongianhap` float)
BEGIN
	#Routine body goes here...
INSERT INTO chitietphieunhap(MaPhieuNhap,MaSach,SoLuongNhap,DonGiaNhap)
VALUES(_maphieunhap,_masach,_soluongnhap,_dongianhap);

END
;;
DELIMITER ;

-- ----------------------------
-- Procedure structure for `sp_ThemPhieuNhap`
-- ----------------------------
DROP PROCEDURE IF EXISTS `sp_ThemPhieuNhap`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_ThemPhieuNhap`(IN `_ngaynhap` date,OUT `_maphieunhap` int)
BEGIN
	#Routine body goes here...
INSERT INTO phieunhap(NgayNhap)VALUES(_ngaynhap);
SET _maphieunhap=LAST_INSERT_ID();
END
;;
DELIMITER ;

-- ----------------------------
-- Procedure structure for `spThemPhieuNhap`
-- ----------------------------
DROP PROCEDURE IF EXISTS `spThemPhieuNhap`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `spThemPhieuNhap`(IN `_ngaynhap` date)
BEGIN
	#Routine body goes here...
INSERT INTO phieunhap(NgayNhap)VALUES(_ngaynhap);
END
;;
DELIMITER ;

-- ----------------------------
-- Indexes structure for table chitiethoadon
-- ----------------------------
CREATE INDEX `FK_Chitiethoadon_HoaDon` USING BTREE ON `chitiethoadon`(`MaHoaDon`) ;
CREATE INDEX `FK_ChiTietHoaDon_Sach` USING BTREE ON `chitiethoadon`(`MaSach`) ;

-- ----------------------------
-- Auto increment value for `chitiethoadon`
-- ----------------------------
ALTER TABLE `chitiethoadon` AUTO_INCREMENT=17;

-- ----------------------------
-- Indexes structure for table chitietphieunhap
-- ----------------------------
CREATE INDEX `FK_ChiTietPhieuNhap_PhieuNhap` USING BTREE ON `chitietphieunhap`(`MaPhieuNhap`) ;
CREATE INDEX `FK_ChiTietPhieuNhap_Sach` USING BTREE ON `chitietphieunhap`(`MaSach`) ;

-- ----------------------------
-- Auto increment value for `chitietphieunhap`
-- ----------------------------
ALTER TABLE `chitietphieunhap` AUTO_INCREMENT=12;

-- ----------------------------
-- Auto increment value for `hoadon`
-- ----------------------------
ALTER TABLE `hoadon` AUTO_INCREMENT=22;

-- ----------------------------
-- Auto increment value for `phieunhap`
-- ----------------------------
ALTER TABLE `phieunhap` AUTO_INCREMENT=43;

-- ----------------------------
-- Indexes structure for table sach
-- ----------------------------
CREATE INDEX `FK_Sach_TheLoai` USING BTREE ON `sach`(`MaTheLoai`) ;
CREATE INDEX `FK_sach_nhaxuatban` USING BTREE ON `sach`(`MaNhaXuatBan`) ;

-- ----------------------------
-- Auto increment value for `theloai`
-- ----------------------------
ALTER TABLE `theloai` AUTO_INCREMENT=4;
